import os
import json
from colorama import Fore, Style
import shutil
from utils import db_pool

MUSIC_ROOT_DIR = '/home/www-data/0_0/音乐'


def traverse_folder(path):
    data = {}
    post_name = ["mp3", "flac", "m4a"]
    for root, dirs, files in os.walk(path):
        for file in files:
            for pn in post_name:
                if file.lower().endswith(pn):
                    if file not in data:
                        data[file] = {"cnt": 1, "path": [root]}
                    else:
                        data[file]["cnt"] += 1
                        data[file]["path"].append(root)

    sql = "select a.id, a.name, a.path, b.tags " \
          + "from music_path a inner join ( " \
          + "    select music_id, GROUP_CONCAT(tag SEPARATOR ',') AS tags " \
          + "    from music_tag group by music_id " \
          + ") b on a.id = b.music_id;"
    all_music = {}
    for music in db_pool.query(sql):
        all_music[music["name"]] = music

    sql = "select id from music_path order by id desc limit 0,1;"
    cnt = db_pool.query(sql)[0]["id"]
    sql_head = "INSERT INTO music_path(id, name, path, created_by, updated_by) VALUES\n"
    sql_head2 = "INSERT INTO music_tag(music_id, tag) VALUES\n"
    items = ""
    items2 = ""
    sqls = []
    sqls2 = []
    for i in data:
        name = i.replace("'", "\\'")
        path = data[i]['path'][0].replace("'", "\\'")
        path = path[len(MUSIC_ROOT_DIR):]
        if name in all_music:
            print(f"{path}/{name} 已存在: {all_music[name]['path']}")
            continue
        cnt += 1
        if "|" in name or "|" in path:
            raise Exception(f"包含特殊字符 {name}, {path}")
        items += f"({cnt}, '{name}', '{path}', 'script', 'script'),\n"
        tag = path[path.rfind("/") + 1:]
        items2 += f"({cnt}, '{tag}'),\n"
        if cnt % 20 == 0:
            sqls.append(sql_head + items[:-2] + ";")
            items = ""
            sqls2.append(sql_head2 + items2[:-2] + ";")
            items2 = ""
        # print(i, data[i]["path"])
        # if data[i]["cnt"] > 1:
        #    print(i, data[i]["path"])
    if items != "":
        sqls.append(sql_head + items[:-2] + ";")
    if items2 != "":
        sqls2.append(sql_head2 + items2[:-2] + ";")
    # print(sqls)
    # print(sqls2)
    for sql in sqls:
        execute_result = db_pool.execute_sql(sql)
        print("执行结果:", execute_result)
    for sql in sqls2:
        execute_result = db_pool.execute_sql(sql)
        print("执行结果:", execute_result)


def query_database(db_pool, mode, value):
    if "n" in mode:
        sql = "select a.id, a.name, a.path, b.tags " \
              + "from music_path a inner join ( " \
              + "    select music_id, GROUP_CONCAT(tag SEPARATOR ',') AS tags " \
              + "    from music_tag group by music_id " \
              + ") b on a.id = b.music_id " \
              + f"where a.name like '%{value}%';"
        return db_pool.query(sql)
    elif "t" in mode:
        sql = "select a.id, a.name, a.path, b.tags " \
              + "from music_path a inner join ( " \
              + "    select music_id, GROUP_CONCAT(tag SEPARATOR ',') AS tags " \
              + f"    from music_tag where tag= '{value}' group by music_id " \
              + ") b on a.id = b.music_id;"
        return db_pool.query(sql)
    elif "n" not in mode and "t" not in mode:
        print("查询模式必须包含n和t其中之一")
    return []


class Up:
    def __init__(self):
        with open(f"{MUSIC_ROOT_DIR}/.up.json", "r") as f:
            self.up_data = json.loads(f.read())

    def write(self):
        with open(f"{MUSIC_ROOT_DIR}/.up.json", "w") as f:
            f.write(json.dumps(self.up_data, ensure_ascii=False, indent=4))

    @classmethod
    def write_data(cls, data):
        with open(f"{MUSIC_ROOT_DIR}/.up.json", "w") as f:
            f.write(json.dumps(data, ensure_ascii=False, indent=4))

    def data(self):
        up_dict = {}
        for up_line in self.up_data:
            line = up_line.split(" ")
            opr, music_ids, tag = line[0], line[1], line[2]
            for music_id in music_ids.split(","):
                music_id = int(music_id)
                if music_id not in up_dict:
                    up_dict[music_id] = []
                up_dict[music_id].append((opr, tag))
        return up_dict


def get_id_set(data):
    result = set()
    for i in data:
        result.add(i["id"])
    return result


class MusicProc:
    def __init__(self):
        with open(f"{MUSIC_ROOT_DIR}/.down.json", "r") as f:
            self.query_data = json.loads(f.read())
        self.up_dict = Up().data()
        self.all_song = []
        for item in self.query_data:
            self.all_song.append(item)
            if item["id"] in self.up_dict:
                old_tags = set(item["tags"].split(","))
                for opr, tag in self.up_dict[item["id"]]:
                    if opr == "a":
                        old_tags.add(tag)
                    elif opr == "d" and tag in old_tags:
                        old_tags.remove(tag)
                item["tags"] = ",".join(list(old_tags))
        self.all_tags = set()
        for item in self.query_data:
            self.all_tags = self.all_tags.union(set(item["tags"].split(",")))

    def query_file_by_tag(self, value):
        result = []
        for item in self.query_data:
            if value.lower() in item["tags"].lower():
                result.append(item)
        return result

    def query_id_by_tag(self, tag):
        return get_id_set(self.query_file_by_tag(tag))

    def query_file_by_name(self, name):
        result = []
        for item in self.query_data:
            if name.lower() in item["name"].lower():
                result.append(item)
        return result

    def query_id_by_name(self, name):
        return get_id_set(self.query_file_by_name(name))

    def query_all(self):
        return self.all_song

    def query_id_all(self):
        return get_id_set(self.query_all())

    def query_song_list_by_id(self, ids):
        result = []
        for i in self.all_song:
            if i["id"] in ids:
                result.append(i)
        return result


def query_file(mode, value):
    mp = MusicProc()
    if "t" in mode:
        return mp.query_file_by_tag(value)
    if "n" in mode:
        return mp.query_file_by_name(value)
    elif "a" in mode:
        return mp.query_all()
    elif "n" not in mode and "t" not in mode:
        print("查询模式必须包含n和t其中之一")
    return []


def is_database():
    with open(f"{MUSIC_ROOT_DIR}/.param.json", "r") as f:
        if json.loads(f.read())["source"] == "on":
            return True
    return False


def query(mode, value):
    if is_database():
        try:
            query_data = query_database(db_pool, mode, value)
        except:
            print("db err, 从本地查询")
            query_data = query_file(mode, value)
    else:
        print("从本地查询")
        query_data = query_file(mode, value)
    result = []
    for i in query_data:
        info = f"{i['id']}\t| {i['name']}\t| {Fore.GREEN}{i['tags']}{Style.RESET_ALL}"
        info2 = f"{i['id']}. {i['name']} [{i['tags']}]"
        result.append(info2)
        if "d" in mode:
            print(f"{info}\t| {i['path']}")
        else:
            print(info)
    return "\n\n".join(result)


def down():
    try:
        sql = "select a.id, a.name, a.path, b.tags " \
              + "from music_path a inner join ( " \
              + "    select music_id, GROUP_CONCAT(tag SEPARATOR ',') AS tags " \
              + "    from music_tag group by music_id " \
              + ") b on a.id = b.music_id;"
        with open(f"{MUSIC_ROOT_DIR}/.down.json", "w+") as f:
            f.write(json.dumps(db_pool.query(sql), ensure_ascii=False))
    except:
        print("database error")
        return
    print("下载成功")


def update_to_file(opr, music_ids, tags):
    up = Up()
    for tag in tags.split(","):
        if tag == "":
            continue
        up.up_data.append(f"{opr} {music_ids} {tag}")
    up.write()
    print("已写入到文件")


def append(music_ids, tag):
    if not is_database():
        print("append, 数据库开关为off，则从本地查询")
        update_to_file("a", music_ids, tag)
        return
    music_ids = music_ids.split(",")
    sql = f"INSERT INTO music_tag(music_id, tag) VALUES\n"
    for music_id in music_ids:
        cnt = db_pool.query(f"select count(1) as cnt from music_tag where music_id={music_id} and tag='{tag}';")
        cnt = cnt[0]["cnt"]
        if cnt > 0:
            continue
        sql += f"({music_id}, '{tag}'),\n"
    if sql.endswith("VALUES\n"):
        info = {"status": "error", "error_message": f"({music_ids},{tag}) have existed"}
        print("执行结果:", info)
        return info
    sql = sql[:-2] + ";"
    execute_result = db_pool.execute_sql(sql)
    print("执行结果:", execute_result)
    return execute_result


def remove(music_ids, tag):
    if not is_database():
        print("remove, 数据库开关为off，则从本地查询")
        update_to_file("d", music_ids, tag)
        return
    sql = f"DELETE FROM music_tag WHERE music_id in({music_ids}) AND tag='{tag}';"
    execute_result = db_pool.execute_sql(sql)
    print("执行结果:", execute_result)
    return execute_result


def add_play_count_to_database(db_pool, _id, cnt):
    sql = f"select p_cnt from music_path where id={_id};"
    res = db_pool.query(sql)
    if len(res) == 0:
        print(f"播放次数更新到数据库: id{_id}不存在")
        return
    _cnt = res[0]["p_cnt"]
    sql = f"update music_path set p_cnt={_cnt + cnt} where id={_id};"
    db_pool.execute_sql(sql)


class PlayCount:
    def __init__(self):
        with open(f"{MUSIC_ROOT_DIR}/.play_count.json", "r") as f:
            self.play_count = json.loads(f.read())

    @classmethod
    def clear(cls):
        with open(f"{MUSIC_ROOT_DIR}/.play_count.json", "w") as f:
            f.write("{}")

    def add(self, _id):
        _id = str(_id)
        if _id not in self.play_count:
            self.play_count[_id] = 1
        else:
            self.play_count[_id] = self.play_count[_id] + 1
        with open(f"{MUSIC_ROOT_DIR}/.play_count.json", "w") as f:
            f.write(json.dumps(self.play_count, ensure_ascii=False, indent=4))


def up():
    if not is_database():
        print("数据库开关为off")
        return
    up = Up()
    fail_line = []
    for up_line in up.up_data:
        line = up_line.split(" ")
        opr, music_ids, tag = line[0], line[1], line[2]
        res = ""
        if opr == "a":
            res = append(music_ids, tag)["status"]
        if opr == "d":
            res = remove(music_ids, tag)["status"]
        if res != "success":
            fail_line.append(up_line)
    up.write_data(fail_line)
    pc = PlayCount()
    for cnt_line in pc.play_count:
        add_play_count_to_database(db_pool, cnt_line, pc.play_count[cnt_line])
    pc.clear()


def export():
    query_data = []
    if not is_database():
        print("从本地查询")
        query_data = query_file("t", "常听")
    for i in query_data:
        shutil.copy(f"{i['path']}/{i['name']}", "/home/sivan/音乐/常听")
    print("导出完成")


def turn(value):
    if value not in ["on", "off"]:
        print(f"参数错误: {value}")
        return
    with open(f"{MUSIC_ROOT_DIR}/.param.json", "w+") as f:
        f.write(json.dumps({"source": value}, ensure_ascii=False))


def add_play_count_to_file(_id):
    pc = PlayCount()
    pc.add(_id)
    print("play_count, 已写入到文件")


def add_play_count(_id):
    if is_database():
        try:
            add_play_count_to_database(db_pool, _id, 1)
        except:
            print("db err, 播放次数更新到本地")
            add_play_count_to_file(_id)
    else:
        print("播放次数更新到本地")
        add_play_count_to_file(_id)


def query_lyrics(_id):
    song_list = query_file("a", "")
    for song in song_list:
        if _id != song["id"]:
            continue
        for typ in ["lrc", "Lrc", "lRc", "LRc", "lrC", "LrC", "lRC", "LRC"]:
            song_name = song["name"][:song["name"].rfind(".")]
            lrc_path = f'{MUSIC_ROOT_DIR}{song["path"]}/{song_name}.{typ}'
            if not os.path.exists(lrc_path):
                continue
            for code in ["utf-8", "gbk"]:
                try:
                    with open(lrc_path, "r", encoding=code) as f:
                        return f.read()
                except Exception as err:
                    print(f"query_lyrics: {err}")
                    pass
    return ""


# www.gequbao.com
if __name__ == "__main__":
    first = sys.argv[1]
    if first == "help":
        print("""init: 初始化
turn: [数据源, on为查数据库，off为查本地]
   q: [模式，n按名称查询，t按标签查询，d查询详细] [名称或标签]
   a: [id] [tag] -- 添加标签, 多个id或tag以英文逗号分隔
   d: [id] [tag] -- 删除标签, 多个id或tag以英文逗号分隔
expt: 导出
down: 下载数据库到本地
  up: 上传本地数据到数据库""")
    elif first == "init":
        traverse_folder(f"{MUSIC_ROOT_DIR}/music_eight")
    elif first == "q":
        query(sys.argv[2], sys.argv[3])
    elif first == "a":
        append(sys.argv[2], sys.argv[3])
    elif first == "d":
        remove(sys.argv[2], sys.argv[3])
    elif first == "down":
        down()
    elif first == "up":
        up()
    elif first == "expt":
        export()
    elif first == "turn":
        turn(sys.argv[2])
